/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package mybean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author MrMAD
 */
public class Order {

    String email;
    String books;
    String totalQuantity;
    String totalCash;
    String id;
    String status;

    public Order() {
    }

    public Order(String email, String books, String totalQuantity, String totalCash, String id, String status) {
        this.email = email;
        this.books = books;
        this.totalQuantity = totalQuantity;
        this.totalCash = totalCash;
        this.id = id;
        this.status = status;
    }

    public Order(String email, String books, String totalQuantity, String totalCash) {
        this.email = email;
        this.books = books;
        this.totalQuantity = totalQuantity;
        this.totalCash = totalCash;
        this.status = status;
    }

    public String getBooks() {
        return books;
    }

    public void setBooks(String books) {
        this.books = books;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getTotalQuantity() {
        return totalQuantity;
    }

    public void setTotalQuantity(String totalQuantity) {
        this.totalQuantity = totalQuantity;
    }

    public String getTotalCash() {
        return totalCash;
    }

    public void setTotalCash(String totalCash) {
        this.totalCash = totalCash;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public boolean save() {
        String result = "";
        try {
            Connection con = getConnection();

            String insertQuery = "insert into [Order] (CustomerEmail, Books, TotalQuantity, TotalCash, Status) values (?, ?, ?, ?, 'Waiting')";
            PreparedStatement insertStatement = con.prepareStatement(insertQuery);
            insertStatement.setString(1, this.email);
            insertStatement.setString(2, this.books);
            insertStatement.setString(3, this.totalQuantity);
            insertStatement.setString(4, this.totalCash);
            insertStatement.execute();

            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Customer.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    @Override
    public String toString() {
        return "Order{" + "email=" + email + ", books=" + books + ", totalQuantity=" + totalQuantity + ", totalCash=" + totalCash + ", id=" + id + ", status=" + status + '}';
    }    

    public List<Order> getOrder() {
        String result = "";
        try {
            List<Order> list = new ArrayList<Order>();           
            Connection con = getConnection();

            String SQLstring = "SELECT * FROM [Order] ORDER BY OrderID DESC";
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(SQLstring);

            while (rs.next()) {
                list.add(new Order(rs.getString("CustomerEmail"), rs.getString("Books"), rs.getString("TotalQuantity"), rs.getString("TotalCash"), rs.getString("OrderID"), rs.getString("Status")));
            }

            stmt.close();
            con.close();
            return list;

        } catch (SQLException ex) {
            Logger.getLogger(Order.class.getName()).log(Level.SEVERE, null, ex);
            result += ex.getMessage();
        }
        result += "loi";
        return null;
    }
    
    public void getOrder(String id){
        try {
            String result = "";
            Order order = new Order();
            Connection con = getConnection();
            
            String sql = "SELECT * FROM [Order] WHERE OrderID=" + id;
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            
            while(rs.next()){
                this.email = rs.getString("CustomerEmail");
                this.books = rs.getString("Books");
                this.totalQuantity = rs.getString("TotalQuantity");
                this.totalCash = rs.getString("TotalCash");
                this.id = rs.getString("OrderID");
                this.status = rs.getString("Status");                
            }
                        
        } catch (SQLException ex) {
            Logger.getLogger(Order.class.getName()).log(Level.SEVERE, null, ex);
        }        
        
    }

    public void delete(String id) {
        try {
            String result = "";
            Connection con = getConnection();
            
            String SQL = "DELETE FROM [Order] WHERE OrderID='" + id + "';";
            Statement stmt = con.createStatement();
            stmt.executeQuery(SQL);                        
        } catch (SQLException ex) {
            Logger.getLogger(Order.class.getName()).log(Level.SEVERE, null, ex);            
        }
    }
    
    public String update(String id, String status){
        String result = "";
        try {
            Connection con = getConnection();
            String SQL = "UPDATE [Order] SET Status='" + status + "' WHERE OrderID=" + id + "";
            Statement stmt = con.createStatement();
            stmt.executeQuery(SQL);
        } catch (SQLException ex) {
            Logger.getLogger(Order.class.getName()).log(Level.SEVERE, null, ex);
            result += ex.getMessage();
        }
        return result;
    }
    
    public Connection getConnection() {
        try {
            String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=BookStore;user=sa;password=sa";
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection con = DriverManager.getConnection(connectionUrl);

            return con;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Order.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(Order.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }
    
    
}
